# encoding: utf-8
# @author: luzhangdan
# @file: mysql_utils.py
# @time: 2021/10/19 14:36

import pymysql
from sshtunnel import SSHTunnelForwarder
from common.logger import GetLogger

logger = GetLogger().get_logger()


# 通过SSH链接数据库
class DBSSHHelper:
    # 构造函数,初始化数据库连接
    def __init__(self):
        self.server = None
        self.myConfig = None
        self.cursor = None

    # 连接数据库
    def connection_database(self):
        try:
            self.server = SSHTunnelForwarder(
                ssh_address_or_host=("192.168.1.100", 22),  # ssh跳转机的地址
                ssh_username="mysql-aly",  # ssh的用户名
                ssh_password='Szyh-2021',
                local_bind_address=("0.0.0.0", 10022),
                remote_bind_address=("rm-wz9803mf0awg83z580o.mysql.rds.aliyuncs.com", 3306))  # 数据库地址
            self.server.__enter__()
            self.myConfig = pymysql.connect(
                user="cloudlotterydev",  # 数据库登录名
                passwd="szyc2019@",  # 数据库密码
                host="127.0.0.1",  # 必须写127.0.0.1
                db="cloudlotterydev",  # 数据库名称
                port=self.server.local_bind_port,
                cursorclass=pymysql.cursors.DictCursor)  # sql查询结果返回类型：DictCursor 为字典类型， 没有指定为 数组
            self.cursor = self.myConfig.cursor()
        except Exception as e:  # 捕捉异常，并打印
            print(e)
            return False
        return True

    # 关闭数据库
    def close_database(self):
        # 如果数据打开，则关闭；否则没有操作
        if self.cursor:
            self.cursor.close()
        if self.myConfig:
            self.myConfig.close()
        if self.server:
            self.server.__exit__()
        return True

    def sql_select(self, sql):
        # 执行sql
        logger.info(f"执行的SQL是：{sql}")
        self.cursor.execute(sql)
        # 获取sql执行后的结果数据
        data = self.cursor.fetchall()
        # 要关闭游标
        logger.info(f"SQL的执行结果是：{data}")
        self.close_database()
        return data


if __name__ == '__main__':
    db = DBSSHHelper()
    db.connection_database()
    result = db.sql_select("SELECT * FROM device_site WHERE STATION_NAME = '自动化测试站点04'")
    print(result)
    # operator_list = []
    # if result:
    #     print("1")
    # for i in result:
    #     for k, v in i.items():
    #         print(k, v)
    #         if k == "num":
    #             operator_list.append(v)
    for i in result:
        s = i.get("ID")
        print(s)
